<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * MY_DB_mssql_driver
 *
 * DB extension class to give various parsing
 * methods.  Generally speaking, these are
 * standard methods for converting a DB query
 * into an array/result.
 *
 * @author Simon Emms <simon@simonemms.com>
 */
class MY_DB_mssql_driver extends CI_DB_mssql_driver {
    
    
    
    public $connection = 'mssql_connect';
    
    
    
    /**
     * Non-persistent database connection
     * 
     * Identical to parent function, but displays
     * an error
     *
     * @access	private called by the base class
     * @return	resource
     */
    public function db_connect() {
        if(function_exists('mssql_connect')) {
            /* Run the function */
            return parent::db_connect();
        } else {
            return false;
        }
    }
    

    /**
     * Persistent database connection
     * 
     * Identical to parent function, but displays
     * an error if function doesn't exist
     *
     * @access	private called by the base class
     * @return	resource
     */
    public function db_pconnect() {
        /* Change the connection function */
        $this->connection = 'mssql_pconnect';
        if(function_exists('mssql_pconnect')) {
            /* Run the function */
            return parent::db_pconnect();
        } else {
            return false;
        }
    }




    /**
     * Get Rows Returned
     *
     * Get the number of rows returned
     * by a query. Set $reset to true
     * if need to create a separate select
     * immediately after this function
     *
     * I've removed the SQL regex because, if
     * a GROUP exists then COUNT(*) returns the
     * number of grouped rows, which may not
     * be the same as the number of rows.
     *
     * @param bool $reset
     * @param string $sql
     * @return decimal
     */
    public function getRowsReturned($reset = false, $sql = null) {
        /* How is the SQL being generated */
        if(is_null($sql)) {
            /* Compile the SQL from the active record */
            $sql = $this->_compile_select();
        }

        /* Run the query */
        $objDb = $this->query($sql);

        if($reset) { $this->_reset_select(); }

        if($objDb->num_rows() == 0) {
            return (float) 0;
        } else {
            return (float) $objDb->num_rows();
        }
    }




    /**
     * qAssoc
     *
     * Returns an associative array.  Useful
     * for returning many DB rows.
     *
     * @return false/array
     */
    public function qAssoc($objDb = false) {
        $objDb = $this->qExec($objDb);
        if(is_object($objDb) && $objDb->num_rows() > 0) {
            return $objDb->result_array();
        } else {
            return false;
        }
    }




    /**
     * qBool
     *
     * Returns a boolean result from the database.
     *
     * @param object $objDb
     * @return bool
     */
    public function qBool($objDb = false) {
        $objDb = $this->qExec($objDb);
        if(is_object($objDb) && $objDb->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }
    
    
    
    
    
    /**
     * qEnum
     * 
     * Get the enum values of a column as an array
     * 
     * @param string $table
     * @param string $column
     * @param string $db
     * @return array
     */
    public function qEnum($table, $column, $db = null) {
        /* Get database if null */
        if(is_null($db)) { $db = $this->database; }
        
        $this->select('COLUMN_TYPE')
            ->from('INFORMATION_SCHEMA.COLUMNS')
            ->where('TABLE_SCHEMA', $db)
            ->where('TABLE_NAME', $table)
            ->where('COLUMN_NAME', $column);
        
        /* Get the result */
        $result = $this->qResult();
        
        if($result != false) {
            /* Something to get - remove stuff around the data */
            $result = preg_replace('/(^\w+\()|(\)$)/', '', $result);
            
            /* Remove first and last ' */
            $result = preg_replace('/(^\')|(\'$)/', '', $result);
            
            $arrResult = explode('\',\'', $result);
            
            /* Check for any escaped ' */
            if(count($arrResult) > 0) {
                foreach($arrResult as $key => $value) {
                    /* The ' escape character is ' - remove half of all ' */
                    $arrResult[$key] = preg_replace('/(\'{2})/', '\'', $value);
                }
                return $arrResult;
            } else {
                /* Return blank array */
                return array();
            }
        } else {
            /* Return empty array */
            return array();
        }
    }
    




    /**
     * qExec
     *
     * Execute a query. Select statements must
     * be built through the CI DB class.
     *
     * @return object
     */
    protected function qExec($objDb = false) {
        /* Check to see if passed variable is correct */
        if(!$objDb instanceof CI_DB_mssql_result) {
            $sql = $this->_compile_select();
            $objDb = $this->query($sql);
        }
        $this->_reset_select();
        return $objDb;
    }



    /**
     * qFields
     *
     * Show all the fields in a table
     *
     * @param false/array $table
     */
    public function qFields($table) {
        $sql = "SHOW COLUMNS FROM `{$table}`";
        $arrFields = $this->qAssoc($this->query($sql));

        if($arrFields !== false) {
            $arrResult = array();

            foreach($arrFields as $field) {
                $datatype = preg_replace('/^([a-z]+)(.+)?$/i', '\\1', $field['Type']);
                preg_match('/\((\d+)\)/', $field['Type'], $length);
                
                /* If enum, get the values */
                $arrEnum = null;
                if($datatype == 'enum') {
                    $arrEnum = $this->qEnum($table, $field['Field']);
                }
                
                $arrResult[] = array(
                    'field' => $field['Field'], /* Name of the field */
                    'datatype' => $datatype, /* Datatyp0e */
                    'length' => end($length), /* Is the length specified */
                    'null' => strtolower($field['Null']) == 'yes', /* Is null allowed */
                    'default' => $field['Default'],
                    'enum' => $arrEnum,
                    'auto' => strtolower($field['Extra']) == 'auto_increment',
                );
            }
            
            return $arrResult;
        } else {
            return false;
        }
    }




    /**
     * qKey
     *
     * Returns an associative array, with the first column as
     * the array key and the second as the value.  Only works
     * if two columns are selected
     *
     * @return false/array
     */
    public function qKey($objDb = false) {
        $objDb = $this->qExec($objDb);
        if(is_object($objDb) && $objDb->num_rows() > 0) {
            $arrResult = array();
            foreach ($objDb->result_array() as $key => $row) {
                if(count($row) == 1) {
                    /* If selecting one row, the keys are meaningless */
                    $value = array_shift($row);
                    $arrResult[] = $value;
                } else {
                    $key = array_shift($row);
                    $value = array_shift($row);
                    $arrResult[$key] = $value;
                }
            }
            return $arrResult;
        } else {
            return false;
        }
    }



    /**
     * qPage
     *
     * Does the same as the qAssoc function, but pages
     * the results.  Send through the page number and
     * the number of results per page you'd like to see.
     *
     * Not that, if you want to specify the SQL, this
     * the plain SQL string to be passed through, not
     * a mssql result object (like qAssoc() for instance)
     *
     * @param number $pg
     * @param number $perPage
     * @param string $sql
     * @return array
     */
    public function qPage($pg = 1, $perPage = 10, $sql = null) {
        $debug_sql = '';

        if($perPage > 0) {
            $total = $this->getRowsReturned(false, $sql);
            $totalPages = ceil($total / $perPage);

            /* Make sure $pg can't be too low */
            $floor = $pg - 1;
            if($floor < 0) {
                $pg = 1;
                $floor = 0;
            }

            /* Make sure $pg can't be too high */;
            if(($floor * $perPage) >= $total) {
                $floor = floor($total / $perPage);
                if(($total / $perPage) > $floor) {
                    /* Don't increment page if on cusp of page - it will show blank page */
                    $pg = $floor + 1;
                } else {
                    $pg = $floor;
                    $floor--;
                }
            }

            /* Calculate the from */
            $from = $floor * $perPage;

            /* Ensure $from isn't below 0 */
            if($from < 0) { $from = 0; }

            /* Add the limit and execute the query */
            if(is_null($sql)) {
                $this->limit($perPage, $from);
                $arrQuery = $this->qAssoc();
                
                $debug_sql = $this->last_query();
            } else {
                /* Remove any trailing semi-colon */
                $sql = preg_replace('/(\;+)$/', '', $sql);
                $sql .= "\n";
                $sql = $this->_limit($sql, $perPage, $from);

                $objDb = $this->query($sql);
                $arrQuery = $this->qAssoc($objDb);

                $debug_sql = $sql;
            }
        } else {
            /* No need to page the result as per page is less than 1 */
            $arrQuery = $this->qAssoc();
            $total = count($arrQuery);
            $totalPages = 1;
            $pg = 1;
            $perPage = 0;
        }

        if($arrQuery === false) {
            $totalPages = 1;
            $pg = 1;
            $arrQuery = array();
        }

        /* Results - send all the data */
        $arrReturn = array(
            'total' => $total,
            'totalPages' => $totalPages,
            'pg' => $pg,
            'perPage' => $perPage,
            'results' => $arrQuery,
            '_sql' => $debug_sql, /* Only to be used for debugging purposes */
        );

        return $arrReturn;
    }





    /**
     * qResult
     *
     * Returns just one column from one
     * row.  Useful if needing to populate
     * a variable.
     *
     * @return false/string
     */
    public function qResult($objDb = false) {
        $this->limit(1);
        $objDb = $this->qExec($objDb);
        if(is_object($objDb) && $objDb->num_rows() > 0) {
            $arrDb = $objDb->row_array();
            return current($arrDb);
        } else {
            return false;
        }
    }





    /**
     * qSingle
     *
     * Returns an array of one DB result
     *
     * @return false/array
     */
    public function qSingle($objDb = false) {
        if($this->ar_limit === false || $this->ar_limit > 1) { $this->limit(1); }
        $objDb = $this->qExec($objDb);
        if(is_object($objDb) && $objDb->num_rows() > 0) {
            return $objDb->row_array();
        } else {
            return false;
        }
    }
    
    
    
    
    
    
    /**
     * Use Database
     * 
     * Forces database to be used
     * 
     * @param string $db 
     */
    public function use_database($db) {
        $this->database = $db;
        $sql = 'USE '.$this->_escape_identifiers($db);
        $this->query($sql);
    }
    
}
?>